﻿Imports DTO
Imports System.Linq

Public Class HocSinhDAO

    Public Sub addStudent_DAO(ByVal st As HocSinhDTO)
        Dim context As DataProviderDataContext = New DataProviderDataContext()
        Dim hs2 = New HocSinh()

        hs2.MaHS = st.MaHS
        hs2.TenHS = st.TenHS
        hs2.GioiTinh = st.GioiTinh
        hs2.NgaySinh = st.NgaySinh
        hs2.DiaChi = st.DiaChi
        hs2.Email = st.Email
        hs2.MaK = st.MaK

        context.HocSinhs.InsertOnSubmit(hs2)
        context.SubmitChanges()

    End Sub

    Public Function getStudent_DAO(ByVal st As Integer) As DataTable
        Dim dt As DataTable = New DataTable()
        dt.Columns.Add("MaHS")
        dt.Columns.Add("TenHS")
        dt.Columns.Add("NgaySinh")
        dt.Columns.Add("GioiTinh")
        dt.Columns.Add("DiaChi")
        dt.Columns.Add("Email")

        Dim context As DataproviderDataContext = New DataproviderDataContext()
        Dim listst = From HocSinh In context.HocSinhs _
                     Where HocSinh.MaK = st And Not (From hoso In context.HoSoHS _
                             Select hoso.MaHS).Contains(HocSinh.MaHS) _
                     Select HocSinh

        For Each value As HocSinh In listst
            Dim row As DataRow = dt.NewRow()
            row.Item(0) = value.MaHS
            row.Item(1) = value.TenHS
            row.Item(2) = value.NgaySinh
            If value.GioiTinh = 1 Then
                row.Item(3) = "Nam"
            Else
                row.Item(3) = "Nữ"
            End If

            row.Item(4) = value.DiaChi
            row.Item(5) = value.Email

            dt.Rows.Add(row)
        Next
        'Return New DataTable()

        Return dt
    End Function

    Public Sub deleteStudent_DAO(ByVal mahs As Integer)
        Dim context As DataproviderDataContext = New DataproviderDataContext()
        Dim st = (From hocsinh In context.HocSinhs _
                 Where hocsinh.MaHS = mahs _
                 Select hocsinh).SingleOrDefault

        context.HocSinhs.DeleteOnSubmit(st)
        context.SubmitChanges()
    End Sub

    Public Sub UpdateStudent_DAO(ByVal mahs As Integer, ByVal stname As String, ByVal address As String, ByVal email As String, ByVal borndate As Date, ByVal sex As Integer)
        Dim context As DataproviderDataContext = New DataproviderDataContext()

        Dim list = From hocsinh In context.HocSinhs
                   Where hocsinh.MaHS = mahs

        For Each value In list
            value.TenHS = stname
            value.NgaySinh = borndate
            value.GioiTinh = sex
            value.DiaChi = address
            value.Email = email
        Next
        context.SubmitChanges()
    End Sub
End Class
